package stv5;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.Map.Entry;

import stv5.abstracted.Database;
import us.danlib.net.Mysql;

/**
 * The Mysql-based implementation of the 
 * 	SeriesTracker Database
 * 
 * @author Daniel
 *
 */
public class MysqlDatabase implements Database {
	private int lastId = 0; // the highest, assigned series ID
	private Mysql db;	
	private HashMap<Integer, Series> seriesById;
	private LinkedList<Series> seriesOrdered;
	
	protected MysqlDatabase(Mysql db) {
		this.db = db;		
	}
	
	/* (non-Javadoc)
	 * @see stv5.Database#reloadSeries()
	 */
	@Override
	public Collection<Series> reloadSeries() {
		seriesById = new HashMap<Integer, Series>();
		seriesOrdered = new LinkedList<Series>();
		db.reconnect(); // just go ahead and reconnect
		ResultSet rs = db.executeQuery("SELECT * FROM `series` ORDER BY `name` ASC");		
		Series newSeries;
		try {
			while (rs.next()) {
				newSeries = Series.fromDB(rs);
				seriesById.put( newSeries.getId(), newSeries );
				seriesOrdered.add( newSeries );
				
	            if (newSeries.getId() > lastId)
	            	lastId = newSeries.getId();				
			}
		} catch (SQLException e) {
			e.printStackTrace();
			System.err.println(" -> Couldn't load settings");
			System.exit(1);			
		}
		
		return seriesOrdered;
	}
	
	/* (non-Javadoc)
	 * @see stv5.Database#rebuildDb(java.util.HashMap, boolean)
	 */
	@Override
	public void rebuildDb(HashMap<String,String> seriesData, boolean deleteOld) {
		//int now = Settings.getTimeInSecs();
		
		// build the SQL		
		String guid, name, fixedname;
		StringBuffer delete = new StringBuffer();
		delete.append("DELETE FROM `series` WHERE NOT(");
		int curr_i=1;
		StringBuffer update = new StringBuffer();
		update.append("UPDATE `series` SET `guid`=CASE \n");
		StringBuffer insert = new StringBuffer();
		insert.append("INSERT INTO series (`id`,`name`,`guid`,`lastplay`,`lastview`) VALUES ");
		boolean updated=false, inserted=false;
		for (Entry<String, String> e : seriesData.entrySet()) {
			guid = e.getValue();
			name = e.getKey();
			fixedname = name.replace("'", "\\'");
				
			// if we're tracking it, update the guid
			if (hasSeries(name)) {
				update.append("WHEN `name`='" + fixedname + "' THEN '"+guid+"' \n");
				updated = true;
			}
			else {
				// it's new; just INSERT
				insert.append("('"+createNewId()+"','"+
						fixedname+"','"
						+guid+"','-1','0'),");
				inserted = true;
			}			
			
			delete.append("`name`='"+fixedname+"'");
			if (curr_i < seriesData.size()) 
				delete.append(" OR ");
			else
				delete.append(");");
				
			curr_i++;
		}
		
		update.append("ELSE guid END;");
		insert.deleteCharAt( insert.length()-1 );
		db.reconnect();
		if (deleteOld && (updated || inserted)) {
			System.out.println(delete.toString());
			db.executeUpdate(delete.toString());			
		}
		if (updated)
			db.executeUpdate(update.toString());
		if (inserted) {
			System.out.println(insert);
			db.executeUpdate(insert.toString());
		}
	}	
	
	/* (non-Javadoc)
	 * @see stv5.Database#updateSeries(stv5.Series)
	 */
	@Override
	public void updateSeries(Series currSeries) {
		db.reconnect();
		db.executeUpdate( currSeries.formatUpdate() );
	}
	
	@Override
	public Collection<Series> getSeries() {
		return seriesOrdered;
	}	
	
	@Override
	public Series getSeries(int seriesId) {
		return seriesById.get(seriesId);
	}	
	
	@Override
	public boolean hasSeries(int seriesId) {
		return seriesById.containsKey(seriesId);
	}	
	
	@Override
	public boolean hasSeries(String folder) {
		for (Series s : seriesOrdered) {			
			if (s.getName().equalsIgnoreCase(folder)) 
				return true;		
		}		
		return false;
	}	

	@Override
	public int loadOldDuration() {
		ResultSet rs = db.executeQuery("SELECT `value` FROM `settings` " +
				"WHERE `key`='old_duration' LIMIT 1");

		int ret = DEFAULT_OLD_DURATION;
		try {
			rs.next();
			ret = rs.getInt("value");
		} catch (SQLException e) {
			//e.printStackTrace();
			System.err.print("(!OD)");
		}		
		
		return ret;		
	}

	/**
	 * @see #loadColorSetting(String, int...)
	 */
	@Override
	public int[] loadOldColor() {
		return loadColorSetting("old_color", DEFAULT_OLD_COLOR);
	}	
	
	/**
	 * @see #loadColorSetting(String, int...)
	 */
	@Override
	public int[] loadNewColor() {
		return loadColorSetting("new_color", DEFAULT_NEW_COLOR);
	}	
	
	@Override
	public String loadLocalPlayer() {
		ResultSet rs = db.executeQuery("SELECT `value` FROM `settings` " +
		"WHERE `key`='local_player' LIMIT 1");

		String ret = "";
		try {
			rs.next();
			ret = rs.getString("value");
		} catch (SQLException e) {
			//e.printStackTrace();
			System.err.print("(!OD)");
		}		
		
		return ret;		
	}	
	
	/**
	 * Generate a new series ID
	 * @return
	 */
	@Override
	public int createNewId() {
		return ++lastId;
	}	
	
	/**
	 * Load a color from the DB, stored in hex format (eg: cc9900)
	 * 
	 * @param key
	 * @param defaultVal An int[] with three values, representing the
	 * 	default RGB
	 * @return
	 */
	private int[] loadColorSetting(String key, int... defaultVal) {
		ResultSet rs = db.executeQuery("SELECT `value` FROM `settings` " +
				"WHERE `key`='"+key+"' LIMIT 1");

		int[] ret = defaultVal;
		try {
			rs.next();
			String raw = rs.getString("value");
			ret = Settings.hexToInts(raw);
		} catch (SQLException e) {
			//e.printStackTrace();
			System.err.print("(!C)");
		}		
		
		return ret;		
	}
}
